import openpyxl
from client import Client

table_regions = 'regions'
table_communities = 'communities'
table_houses = 'houses'

file_name = 'export.xlsx'
book = None
client = Client()


def do_regions(index: int):
    table = client.get_collection(table_regions)
    res_list = [i for i in table.find({}, {'name': 1, 'region': 1})]

    # 创建sheet
    reg_s = book.create_sheet('区划', index)
    reg_s.cell(1, 1, '区名')
    reg_s.cell(1, 2, '键值')
    for i, item in enumerate(res_list):
        reg_s.cell(i + 2, 1, item['name'])
        reg_s.cell(i + 2, 2, item['region'])


def do_communities(index: int):
    # 创建sheet
    com_s = book.create_sheet('区划-小区', index)
    com_s.cell(1, 1, '区划')
    com_s.cell(1, 2, '小区编号')
    com_s.cell(1, 3, '小区名称')
    com_s.cell(1, 4, '建筑年代')
    com_s.cell(1, 5, '建筑类型')
    com_s.cell(1, 6, '楼栋总数')
    com_s.cell(1, 7, '小区均价')
    com_s.cell(1, 8, '90天成交套数')
    com_s.cell(1, 9, '经度')
    com_s.cell(1, 10, '纬度')

    # 创建配套表
    line_count = 0
    srd_s = book.create_sheet('小区-配套', index + 1)
    srd_s.cell(1, 1, '小区编号')
    srd_s.cell(1, 2, '配套类型')
    srd_s.cell(1, 3, '配套名称')
    srd_s.cell(1, 4, '地址')
    srd_s.cell(1, 5, '距离')
    srd_s.cell(1, 6, '经度')
    srd_s.cell(1, 7, '纬度')

    # 遍历数据
    table = client.get_collection(table_communities)
    com_count = table.count_documents({})
    for i, item in enumerate(table.find({})):
        if i % 50 == 0:
            print(f"遍历小区 {i}/{com_count}")
        com_s.cell(i + 2, 1, item['region'])
        com_s.cell(i + 2, 2, item['cid'])
        com_s.cell(i + 2, 3, item['name'])
        com_s.cell(i + 2, 4, item['build_time'])
        com_s.cell(i + 2, 5, item['building_type'])
        com_s.cell(i + 2, 6, item['building_count'])
        com_s.cell(i + 2, 7, item['avg_price'])
        com_s.cell(i + 2, 8, item['recent_deal_count'])
        com_s.cell(i + 2, 9, item['longitude'])
        com_s.cell(i + 2, 10, item['latitude'])

        srds = item['surroundings']
        do_surrounding(line_count, srd_s, srds, item['cid'])
        line_count += len(srds)


def do_surrounding(offset, sheet, items: list, cid):
    for i, item in enumerate(items):
        sheet.cell(i + offset + 2, 1, cid)
        sheet.cell(i + offset + 2, 2, item['srd_type'])
        sheet.cell(i + offset + 2, 3, item['srd_name'])
        sheet.cell(i + offset + 2, 4, item['srd_addr'])
        sheet.cell(i + offset + 2, 5, item['srd_distance'])
        sheet.cell(i + offset + 2, 6, item['longitude'])
        sheet.cell(i + offset + 2, 7, item['latitude'])


def do_house(index):
    # 创建sheet
    hos_s = book.create_sheet('小区-房屋', index)
    hos_s.cell(1, 1, '小区编号')
    hos_s.cell(1, 2, '房屋编号')
    hos_s.cell(1, 3, '房屋户型')
    hos_s.cell(1, 4, '建筑面积')
    hos_s.cell(1, 5, '套内面积')
    hos_s.cell(1, 6, '房屋朝向')
    hos_s.cell(1, 7, '装修情况')
    hos_s.cell(1, 8, '配备电梯')
    hos_s.cell(1, 9, '所在楼层')
    hos_s.cell(1, 10, '户型结构')
    hos_s.cell(1, 11, '建筑类型')
    hos_s.cell(1, 12, '建筑结构')
    hos_s.cell(1, 13, '梯户比例')
    hos_s.cell(1, 14, '成交价格')
    hos_s.cell(1, 15, '挂牌价')
    hos_s.cell(1, 16, '单价(元/平方米)')
    hos_s.cell(1, 17, '成交时间')
    hos_s.cell(1, 18, '成交周期')
    hos_s.cell(1, 19, '建筑年代')
    hos_s.cell(1, 20, '周边配套描述')
    hos_s.cell(1, 21, '交通出行描述')

    # 遍历数据
    table = client.get_collection(table_houses)
    hos_count = table.count_documents({})
    for i, item in enumerate(table.find({})):
        if i % 100 == 0:
            print(f"遍历房屋 {i}/{hos_count}")
        hos_s.cell(i + 2, 1, item['cid'])
        hos_s.cell(i + 2, 2, item['hid'])
        hos_s.cell(i + 2, 3, item['model'])
        hos_s.cell(i + 2, 4, item['design_area'])
        hos_s.cell(i + 2, 5, item['real_area'])
        hos_s.cell(i + 2, 6, item['face_direction'])
        hos_s.cell(i + 2, 7, item['decoration'])
        hos_s.cell(i + 2, 8, item['has_lift'])
        hos_s.cell(i + 2, 9, item['floor'])
        hos_s.cell(i + 2, 10, item['structure'])
        hos_s.cell(i + 2, 11, item['building_type'])
        hos_s.cell(i + 2, 12, item['building_structure'])
        hos_s.cell(i + 2, 13, item['lift_ratio'])
        hos_s.cell(i + 2, 14, item['deal_value'])
        hos_s.cell(i + 2, 15, item['origin_value'])
        hos_s.cell(i + 2, 16, item['price'])
        hos_s.cell(i + 2, 17, item['deal_time'])
        hos_s.cell(i + 2, 18, item['deal_duration'])
        hos_s.cell(i + 2, 19, item['build_time'])
        hos_s.cell(i + 2, 20, item['desc_surroundings'])
        hos_s.cell(i + 2, 21, item['desc_transportation'])


if __name__ == '__main__':
    book = openpyxl.Workbook()
    do_regions(0)
    do_communities(1)
    do_house(3)

    book.save(file_name)
